import pandas as pd
import numpy as np
import os
from typing import List

data=pd.read_excel('综合信息查询_组合证券314_20200422.xls')
position=list(data['持仓'])
buypos=list(data['当日买量'])
stocks=list(data['证券代码'])
names=list(data['证券名称'])
mv=list(data['市值'])
price=list(data['最新价'])
account=list(data['组合编号'])
codeList=[]
for i in range(len(stocks)):
    if np.isnan(stocks[i]):
        continue
        
    code=int(stocks[i])
    pos=position[i]
    #pos=position[i]-buypos[i]
    #pos=np.floor(pos*1/100)*100
    codeStr=str(code).rjust(6,'0')
    acc=int(account[i])
    if ((codeStr<'009999')|((codeStr>='300000') & (codeStr<='309999'))):
        codeStr+='.SZ'
    elif ((codeStr>='600000') & (codeStr<='609999')):
        codeStr+='.SH'
    else:
        continue
    codeList.append({'code':codeStr,'account':acc,'hold':pos,'name':names[i],'marketValue':mv[i],'price':price[i]})
allStocks=pd.DataFrame(codeList)

allStocks.to_csv('t02_20200422.csv')
para=pd.read_csv('parameter20200422.csv')


myStocks=allStocks[allStocks['account']==300140013]

print(300140013)
deleteCodes=[]
myStocks=myStocks[~myStocks['code'].isin(deleteCodes)]
stocksdf=[]
controldf=[]
for code in list(myStocks['code'].unique()):
    para1=0.004
    para2=0.4
    if code in (list(para['code'])):
        select=para[para['code']==code].iloc[0]
        para1=select['parameter1']
        para2=select['parameter2']
    mycode=myStocks[myStocks['code']==code].iloc[0]
    hold=mycode['hold']
    hold=np.floor(hold/100.0)*100
    if (hold>200):
        stocksdf.append({'code':code,'hold':hold,'parameter1':para1,'parameter2':para2})
    pass
stocksdf=pd.DataFrame(data=stocksdf)
stocksdf.to_csv("stockT0220200422.csv",index=0,header=0)
control=stocksdf[['code','hold']]
control.to_csv("controlT0220200422.csv",index=0,header=0)
if myStocks.shape[0]>0:
    stocks=myStocks.set_index(['code'])
    stocks['parameter1']=0.004
    stocks['parameter2']=0.4
    mv=(stocks['hold']*stocks['price']).sum()
    mv=stocks['marketValue'].sum()
    print(mv)
    stocks=stocks.reset_index();
    stocks['hold']=np.floor(stocks['hold']/100.0)*100
    #stocks=stocks[stocks['hold']>=200]
    #stocks=stocks[stocks['price']>=5]
    mv=(stocks['hold']*stocks['price']).sum()
    print(mv)
    stocks=stocks[['code','hold','parameter1','parameter2']]
    #stocks.to_csv("stockT0220200422.csv",index=0,header=0)
    control=stocks[['code','hold']]
    #control.to_csv("controlT0220200422.csv",index=0,header=0)
print("+++++++++++++++++++++++++++++++++++++++++")








